package com.deng.fankui.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.hql.ast.tree.IsNotNullLogicOperatorNode;

import com.deng.common.SQLHelper;
import com.deng.fankui.dao.IFanKui;
import com.deng.fankui.vo.FanKuiOrganizationVo;
import com.deng.util.DataFormater;
import com.deng.util.DateTools;

public class FanKuiImpl extends SQLHelper implements IFanKui {

	public List<FanKuiOrganizationVo> getFanKuiKeshiList(String stat_time_str) {
		try
		{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select o.name," +
					" o.parent_id,o.id as organization_id," +
					" v.id,v.stat_time,v.type," +
					" v.koufen,v.defen,v.xiaoyi,v.jintie,v.zongfen," +
					" v.standard_result,v.reward_result,v.deny_result,v.other_result,v.opinion" +
					" from t_sys_stat_department as o" +
					" left join" +
					" FANKUI_ORGANIZATION as v" +
					" on" +
					" o.id = v.organization_id" +
					" and v.type = 1" +
					" and stat_time = '" +stat_time_str+"'"+
					" where o.is_leaf = 1" +
					" and o.parent_id = 2" +
					" order by name" );
			List<FanKuiOrganizationVo> rtL = new ArrayList<FanKuiOrganizationVo>();
			while(rs.next()){
				FanKuiOrganizationVo vo = new FanKuiOrganizationVo();
				vo.id = rs.getInt("id");
				vo.organization_id = rs.getInt("organization_id");	
				vo.organization_name = rs.getString("name");
				vo.stat_time = DateTools.stdJustDate(stat_time_str);
				vo.stat_time_str = stat_time_str;
				vo.type = 1;
				vo.zongfen = DataFormater.numberToString(rs.getBigDecimal("zongfen"));
				vo.defen = DataFormater.numberToString(rs.getBigDecimal("defen"));
				vo.koufen = DataFormater.numberToString(rs.getBigDecimal("koufen"));
				vo.xiaoyi = DataFormater.numberToString(rs.getBigDecimal("xiaoyi"));
				vo.jintie = DataFormater.numberToString(rs.getBigDecimal("jintie"));
				vo.standard_result = rs.getString("standard_result");
				vo.reward_result = rs.getString("reward_result");
				vo.deny_result = rs.getString("deny_result");
				vo.other_result = rs.getString("other_result");
				vo.opinion = rs.getString("opinion");
				rtL.add(vo);
			}
			freeConnection(conn);
			return rtL;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return null;
	}
	public List<FanKuiOrganizationVo> getFanKuiKezhurenList(String stat_time_str) {
		try
		{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select o.name," +
					" o.parent_id,o.id as organization_id," +
					" v.id,v.stat_time,v.type," +
					" v.koufen,v.defen,v.xiaoyi,v.jintie,v.zongfen," +
					" v.standard_result,v.reward_result,v.deny_result,v.other_result,v.opinion" +
					" from t_sys_stat_department as o" +
					" left join" +
					" FANKUI_ORGANIZATION as v" +
					" on" +
					" o.id = v.organization_id" +
					" and v.type = 2" +
					" and stat_time = '" +stat_time_str+"'"+
					" where o.is_leaf = 1" +
					" and o.parent_id = 3" +
					" order by name");
			List<FanKuiOrganizationVo> rtL = new ArrayList<FanKuiOrganizationVo>();
			while(rs.next()){
				FanKuiOrganizationVo vo = new FanKuiOrganizationVo();
				vo.id = rs.getInt("id");
				vo.organization_id = rs.getInt("organization_id");	
				vo.organization_name = rs.getString("name");
				vo.stat_time = DateTools.stdJustDate(stat_time_str);
				vo.stat_time_str = stat_time_str;
				vo.type = 2;
				vo.zongfen = DataFormater.numberToString(rs.getBigDecimal("zongfen"));
				vo.defen = DataFormater.numberToString(rs.getBigDecimal("defen"));
				vo.koufen = DataFormater.numberToString(rs.getBigDecimal("koufen"));
				vo.xiaoyi = DataFormater.numberToString(rs.getBigDecimal("xiaoyi"));
				vo.jintie = DataFormater.numberToString(rs.getBigDecimal("jintie"));
				vo.standard_result = rs.getString("standard_result");
				vo.reward_result = rs.getString("reward_result");
				vo.deny_result = rs.getString("deny_result");
				vo.other_result = rs.getString("other_result");
				vo.opinion = rs.getString("opinion");
				rtL.add(vo);
			}
			freeConnection(conn);
			return rtL;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return null;
	}
	public FanKuiOrganizationVo reloadKeShiFankui(FanKuiOrganizationVo fkVo) {
		try
		{
			Connection conn = getConnect();
			
			BigDecimal totalPoint = new BigDecimal(0);//总分
			BigDecimal defen = new BigDecimal(0);//得分
			BigDecimal koufen = new BigDecimal(0);//扣分
			BigDecimal xiaoyi = new BigDecimal(0);//效益浮动
			BigDecimal yuan = new BigDecimal(0);//yuan
			BigDecimal yuanmeiren = new BigDecimal(0);//yuan/ren
			StringBuffer koufenSB = new StringBuffer();//扣分说明
			StringBuffer rewardSB = new StringBuffer();//奖惩说明
			StringBuffer denySB = new StringBuffer();//一票否决说明
			
			//扣分原因
			String sql = "select distinct a.display_name,a.point,frequence," +
					"	case 	" +
					"		when update_score is not null then update_score" +
					"		when frequence = '0001' and month != 12 then point" +
					" 		when frequence = '0002' and month not in(6,12) then point" +
					"		when frequence = '0003' and month not in(3,6,9,12) then point" +
					"		when score is null then 0" +
					"		else score end as update_score," +
//					"	case	" +
//					"		when frequence='0001' then '年' " +
//					"		when frequence='0002' then '半年'" +
//					"		when frequence='0003' then '季度'" +
//					"		when frequence='0004' then '月'" +
//					"	else '' end as frequence_name," +
					"	case " +
					"		when update_score is not null then update_score" +
					"		when score is null then 0" +
					"		else score end as score," +
					"	b.kpi_id,b.kaohe_id," +
					"	a.description,b.value,a.detail_type" +
					"	from KPI_VS_ORGANIZATION as a" +
					"	left join" +
					"	KPI_Value as b" +
					"	on a.kpi_id = b.kpi_id" +
					"	and a.kaohe_id = b.kaohe_id	" +
					"	and year = " +(fkVo.stat_time_str.split("-")[0])+
					"	and month =" +(fkVo.stat_time_str.split("-")[1])+
					"	where" +
					"	a.kaohe_id = " +fkVo.organization_id+
					"	and standard_type = 0" +
					"	order by frequence desc,display_name";

//			String sql = "select vs.kaohe_id,vs.kpi_id," +
//				" d.id,d.item_name,d.display_name,d.description,d.standard_type,d.detail_type,d.frequence," +
//				" d.point ,v.value,v.score,v.update_score,v.detail,v.valid_detail,v.valid" +
//				" from(" +
//				" select distinct kaohe_id,kpi_id from KPI_VS_ORGANIZATION" +
//				" where kaohe_id = " +fkVo.organization_id+
//				" ) as vs" +
//				" left join DICT_KPI_ITEM as d" +
//				" on d.id = vs.kpi_id" +
//				" left join KPI_VALUE as v" +
//				" on cast(cast(v.year as char)+'-'+cast(v.month as char)+'-1' as datetime) = '"+fkVo.stat_time_str+"'" +
//				" and v.kpi_id = d.id" +
//				" and v.kaohe_id = vs.kaohe_id" +
//	//			" and v.valid = 1" +
//				" where d.standard_type = 0" ;
			ResultSet rs = queryData(conn,sql);
			
			int x = 0;
			while(rs.next()){
				BigDecimal p_point = rs.getBigDecimal("point");
				BigDecimal p_score = rs.getBigDecimal("score");
				BigDecimal p_update_score = rs.getBigDecimal("update_score");
				BigDecimal p_value = rs.getBigDecimal("value");
				String p_description = rs.getString("description");
				String p_display_name = rs.getString("display_name");
				String frequence = rs.getString("frequence");
				int detail_type = rs.getInt("detail_type");
				int kpi_id = rs.getInt("kpi_id");
				int kaohe_id = rs.getInt("kaohe_id");
				
				if(isFullPoint(fkVo.stat_time, frequence))//非本季度，给满分
				{
					totalPoint = totalPoint.add(p_point).setScale(2,BigDecimal.ROUND_HALF_UP);					
				}
				else
				{
					if(p_point != null)
					{
						totalPoint = totalPoint.add(p_point).setScale(2,BigDecimal.ROUND_HALF_UP);
					}
					
					if(p_update_score != null)
					{
						defen = defen.add(p_update_score).setScale(2,BigDecimal.ROUND_HALF_UP);
						koufen = koufen.add(p_point.subtract(p_update_score).setScale(2,BigDecimal.ROUND_HALF_UP)).setScale(2,BigDecimal.ROUND_HALF_UP);
					}
					else if(p_score != null)
					{
						defen = defen.add(p_score).setScale(2,BigDecimal.ROUND_HALF_UP);
						koufen = koufen.add(p_point.subtract(p_score).setScale(2,BigDecimal.ROUND_HALF_UP)).setScale(2,BigDecimal.ROUND_HALF_UP);
					}
					else
					{
						koufen = koufen.add(p_point).setScale(2,BigDecimal.ROUND_HALF_UP);
					}
					
					
					
					if(p_point != null && p_score != null)
					{
						BigDecimal b = new BigDecimal(0);
						if(p_update_score != null)
						{
							b = p_update_score;
						}
						else
						{
							b = p_score;
						}
						if(p_point.compareTo(b)!=0)
						{
							x++;
							koufenSB.append(x+". "+p_display_name+" 扣"+(p_point.subtract(b).setScale(2,BigDecimal.ROUND_HALF_UP))+"分。 ["+p_description+"]\r");
							if(detail_type == 2)//明细
							{
								String sql2 = "select vs.kaohe_id," +
										"	(select KPI_DETAIL_VS_ORGANIZATION.parent_id from KPI_DETAIL_VS_ORGANIZATION where kpi_detail_id = vs.kpi_detail_id and kaohe_id = vs.kaohe_id) as kpi_id,vs.kpi_detail_id," +
										"	(select display_name from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as display_name,  " +
										"	(select target_value from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as target_value," +
										"	(select danwei from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as danwei," +
										"	vs.value,vs.score     from KPI_DETAIL_VALUE as vs" +
										"	where cast(cast(vs.year as char)+'-'+cast(vs.month as char)+'-1' as datetime) = '"+fkVo.stat_time_str+"'" +
										"  	and score < 0" +
										"	and kpi_detail_id in" +
										"	(select kpi_detail_id from KPI_DETAIL_VS_ORGANIZATION	where parent_id = "+kpi_id+" and kaohe_id = "+kaohe_id+")	 ";
									
									
									
//									"select vs.kaohe_id,vs.kpi_id,vs.kpi_detail_id," +
//									"	(select display_name from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as display_name," +
//									"	(select target_value from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as target_value," +
//									"	(select danwei from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as danwei," +
//									"	value,score" +
//									"	from KPI_VS_ORGANIZATION  as vs" +
//									"	left join KPI_DETAIL_VALUE as v" +
//									"	on vs.kpi_detail_id = v.kpi_detail_id" +
//									"	and cast(cast(v.year as char)+'-'+cast(v.month as char)+'-1' as datetime) = '"+fkVo.stat_time_str+"'" +
//									"	where score <= 0";
								ResultSet rs2 = queryData(conn, sql2);
								while(rs2.next())
								{
									String c_display_name = rs2.getString("display_name");
									BigDecimal target_value = rs2.getBigDecimal("target_value");
									BigDecimal c_value = rs2.getBigDecimal("value");
									BigDecimal c_score = rs2.getBigDecimal("score");
									koufenSB.append("     "+c_display_name+" 目标值："+target_value.setScale(2,BigDecimal.ROUND_HALF_UP)+"，实际值："+c_value+"，扣除"+c_score+"分。\r");
								}
							}
						}		
					}
				}
			}
			
			//单项奖惩 得分不等于0就有要统计
			sql = "select distinct a.display_name,a.point,frequence," +
				"	case 	" +
				"		when update_score is not null then update_score" +
				"		when frequence = '0001' and month != 12 then point" +
				" 		when frequence = '0002' and month not in(6,12) then point" +
				"		when frequence = '0003' and month not in(3,6,9,12) then point" +
				"		when score is null then 0" +
				"		else score end as update_score," +
	//			"	case	" +
	//			"		when frequence='0001' then '年' " +
	//			"		when frequence='0002' then '半年'" +
	//			"		when frequence='0003' then '季度'" +
	//			"		when frequence='0004' then '月'" +
	//			"	else '' end as frequence_name," +
				"	case " +
				"		when update_score is not null then update_score" +
				"		when score is null then 0" +
				"		else score end as score," +
				"	b.kpi_id,b.kaohe_id," +
				"	a.description,b.value,a.detail_type,a.section_detail,a.detail_type," +
				"	(select danwei from DICT_KPI_ITEM where id = a.kpi_id) as danwei" +
				"	from KPI_VS_ORGANIZATION as a" +
				"	left join" +
				"	KPI_Value as b" +
				"	on a.kpi_id = b.kpi_id" +
				"	and a.kaohe_id = b.kaohe_id	" +
				"	and year = " +(fkVo.stat_time_str.split("-")[0])+
				"	and month =" +(fkVo.stat_time_str.split("-")[1])+
				"	where" +
				"	a.kaohe_id = " +fkVo.organization_id+
				"	and standard_type = 1" +
				"	order by frequence desc,display_name";
			
			
			rs = queryData(conn, sql);
			
			x = 0;
			while(rs.next()){	
//				0：元     [区间（第5个）]
//				1：元/人
//				2：效益％
				BigDecimal p_score = rs.getBigDecimal("score");
				BigDecimal p_update_score = rs.getBigDecimal("update_score");
				BigDecimal p_value = rs.getBigDecimal("value");
				String section = rs.getString("section_detail");
				String frequence = rs.getString("frequence");
				int danwei = rs.getInt("danwei");
				String danwei_s = "";
				String p_description = rs.getString("description");
				String p_display_name = rs.getString("display_name");
				int detail_type = rs.getInt("detail_type");
				int kpi_id = rs.getInt("kpi_id");
				int kaohe_id = rs.getInt("kaohe_id");
				
				
				if(isFullPoint(fkVo.stat_time, frequence))
				{
					continue;
				}
				
				if(p_score != null)//得分非空
				{
					BigDecimal tmpScore = new BigDecimal(0);
					if(p_update_score != null && p_update_score.compareTo(tmpScore)!=0)
					{
						tmpScore = p_update_score.setScale(2,BigDecimal.ROUND_HALF_UP);
					}
					else if(p_score.compareTo(tmpScore)!=0)
					{
						tmpScore = p_score.setScale(2,BigDecimal.ROUND_HALF_UP);
					}
					else//score = 0,不用处理
					{
						continue;
					}

					if(detail_type == 0)//标准型
					{
						if(danwei == 0)
						{
							danwei_s = "元";
							yuan = yuan.add(tmpScore).setScale(2,BigDecimal.ROUND_HALF_UP);
						}
						else if(danwei == 1)
						{
							danwei_s = "元/人";
							yuanmeiren = yuanmeiren.add(tmpScore).setScale(2,BigDecimal.ROUND_HALF_UP);
						}
						else if(danwei == 2)
						{
							danwei_s = "效益％";
							xiaoyi = xiaoyi.add(tmpScore).setScale(2,BigDecimal.ROUND_HALF_UP);
						}
						x++;
						rewardSB.append(x+". "+p_display_name+" "+tmpScore+danwei_s+"。 ["+p_description+"]\r");
					}
					else if(detail_type == 1)//区间型
					{
						String dw = section.split(";")[0].split(",")[4];
						if(dw.equals("0"))
						{
							danwei_s = "元";
							yuan = yuan.add(tmpScore).setScale(2,BigDecimal.ROUND_HALF_UP);
						}
						else if(dw.equals("1"))
						{
							danwei_s = "元/人";
							yuanmeiren = yuanmeiren.add(tmpScore).setScale(2,BigDecimal.ROUND_HALF_UP);
						}
						else if(dw.equals("2"))
						{
							danwei_s = "效益％";
							xiaoyi = xiaoyi.add(tmpScore).setScale(2,BigDecimal.ROUND_HALF_UP);
						}
						x++;
						rewardSB.append(x+". "+p_display_name+" "+tmpScore+danwei_s+"。 ["+p_description+"]\r");
						
					}
					else//明细型
					{
						x++;
						rewardSB.append(x+". "+p_display_name+" "+tmpScore+danwei_s+"。 ["+p_description+"]\r");
						String sql2 = "select vs.kaohe_id," +
						"	(select KPI_DETAIL_VS_ORGANIZATION.parent_id from KPI_DETAIL_VS_ORGANIZATION where kpi_detail_id = vs.kpi_detail_id and kaohe_id = vs.kaohe_id) as kpi_id,vs.kpi_detail_id," +
						"	(select display_name from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as display_name,  " +
						"	(select target_value from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as target_value," +
						"	(select danwei from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as danwei," +
						"	vs.value,vs.score,vs.update_score     from KPI_DETAIL_VALUE as vs" +
						"	where cast(cast(vs.year as char)+'-'+cast(vs.month as char)+'-1' as datetime) = '"+fkVo.stat_time_str+"'  and score < 0" +
						"	and kpi_detail_id in" +
						"	(select kpi_detail_id from KPI_DETAIL_VS_ORGANIZATION	where parent_id = "+kpi_id+" and kaohe_id = "+kaohe_id+")	 ";
						
//						String sql2 = "select vs.kaohe_id,vs.kpi_id,vs.kpi_detail_id," +
//							"	(select display_name from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as display_name," +
//							"	(select target_value from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as target_value," +
//							"	(select danwei from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as danwei," +
//							"	value,score,update_score" +
//							"	from KPI_VS_ORGANIZATION  as vs" +
//							"	left join KPI_DETAIL_VALUE as v" +
//							"	on vs.kpi_detail_id = v.kpi_detail_id" +
//							"	and cast(cast(v.year as char)+'-'+cast(v.month as char)+'-1' as datetime) = '"+fkVo.stat_time_str+"'" +
//							"	where score != 0";
						ResultSet rs2 = queryData(conn, sql2);
						while(rs2.next())
						{
							String c_display_name = rs2.getString("display_name");
							BigDecimal target_value = rs2.getBigDecimal("target_value");
							BigDecimal c_value = rs2.getBigDecimal("value");
							BigDecimal c_score = rs2.getBigDecimal("score");
							BigDecimal c_update_score = rs2.getBigDecimal("update_score");
							int c_danwei = rs2.getInt("danwei");
							BigDecimal tmp_score = new BigDecimal(0);
							if(c_update_score !=null && c_update_score.compareTo(tmp_score)!=0)
							{
								tmp_score = c_update_score.setScale(2,BigDecimal.ROUND_HALF_UP);
							}
							else if(c_score !=null && c_score.compareTo(tmp_score)!=0)
							{
								tmp_score = c_score.setScale(2,BigDecimal.ROUND_HALF_UP);
							}

							if(c_danwei == 0)
							{
								rewardSB.append("     "+c_display_name+" "+tmp_score+"元。\r");
							}
							else if(c_danwei == 1)
							{
								rewardSB.append("     "+c_display_name+" "+tmp_score+"元/人。\r");
							}
							else
							{
								rewardSB.append("     "+c_display_name+" "+tmp_score+"效益％。\r");
							}
							
						}
					}
				}
			}
			
			//一票否决
			sql = "select distinct a.display_name,a.point,frequence," +
				"	case 	" +
				"		when update_score is not null then update_score" +
				"		when frequence = '0001' and month != 12 then point" +
				" 		when frequence = '0002' and month not in(6,12) then point" +
				"		when frequence = '0003' and month not in(3,6,9,12) then point" +
				"		when score is null then 0" +
				"		else score end as update_score," +
	//			"	case	" +
	//			"		when frequence='0001' then '年' " +
	//			"		when frequence='0002' then '半年'" +
	//			"		when frequence='0003' then '季度'" +
	//			"		when frequence='0004' then '月'" +
	//			"	else '' end as frequence_name," +
				"	case " +
				"		when update_score is not null then update_score" +
				"		when score is null then 0" +
				"		else score end as score," +
				"	b.kpi_id,b.kaohe_id," +
				"	a.description,b.value,a.detail_type,a.detail_type," +
				"	(select danwei from DICT_KPI_ITEM where id = a.kpi_id) as danwei" +
				"	from KPI_VS_ORGANIZATION as a" +
				"	left join" +
				"	KPI_Value as b" +
				"	on a.kpi_id = b.kpi_id" +
				"	and a.kaohe_id = b.kaohe_id	" +
				"	and year = " +(fkVo.stat_time_str.split("-")[0])+
				"	and month =" +(fkVo.stat_time_str.split("-")[1])+
				"	where" +
				"	a.kaohe_id = " +fkVo.organization_id+
				"	and standard_type = 2" +
				"	order by frequence desc,display_name";
			
//			sql = "select vs.kaohe_id,vs.kpi_id," +
//				" d.id,d.item_name,d.display_name,d.description,d.standard_type,d.detail_type,d.frequence,d.danwei," +
//				" d.point ,v.value,v.score,v.update_score,v.detail,v.valid_detail,v.valid" +
//				" from(" +
//				" select distinct kaohe_id,kpi_id from KPI_VS_ORGANIZATION" +
//				" where kaohe_id = " +fkVo.organization_id+
//				" ) as vs" +
//				" left join DICT_KPI_ITEM as d" +
//				" on d.id = vs.kpi_id" +
//				" left join KPI_VALUE as v" +
//				" on cast(cast(v.year as char)+'-'+cast(v.month as char)+'-1' as datetime) = '"+fkVo.stat_time_str+"'" +
//				" and v.kpi_id = d.id" +
//				" and v.kaohe_id = vs.kaohe_id" +
//	//			" and v.valid = 1" +
//				" where d.standard_type = 2" ;
			rs = queryData(conn,sql);
			
			x = 0;
			while(rs.next()){
				BigDecimal p_score = rs.getBigDecimal("score");
				BigDecimal p_update_score = rs.getBigDecimal("update_score");
				BigDecimal p_value = rs.getBigDecimal("value");
				String p_description = rs.getString("description");
				String p_display_name = rs.getString("display_name");
				String frequence = rs.getString("frequence");
				int detail_type = rs.getInt("detail_type");
				int danwei = rs.getInt("danwei");
				String danwei_s = "";
				if(danwei == 0)
				{
					danwei_s = "元";
				}
				else if(danwei == 1)
				{
					danwei_s = "元/人";
				}
				else
				{
					danwei_s = "效益％";
				}
				
				if(isFullPoint(fkVo.stat_time, frequence))
				{
					continue;
				}
				else
				{
					BigDecimal tmp_score = new BigDecimal(0);
					if(detail_type == 0)//基本
					{
						if(p_update_score != null && p_update_score.compareTo(tmp_score)!=0)
						{
							tmp_score = p_update_score.setScale(2,BigDecimal.ROUND_HALF_UP);
						}
						else if(p_score != null && p_score.compareTo(tmp_score)!=0)
						{
							tmp_score = p_score.setScale(2,BigDecimal.ROUND_HALF_UP);
						}
						else
						{
							continue;
						}
						x++;
						denySB.append(x+". "+p_display_name+"   提交"+p_value+"，  "+tmp_score+danwei_s+"。 ["+p_description+"]\r");
					}
					else if(p_value != null && p_value.compareTo(tmp_score)!=0)//处理意见
					{
						x++;
						denySB.append(x+". "+p_display_name+"   发生"+p_value+"起。["+p_description+"]\r");
					}
				}
				
				
			}
			
			
			
			
			
			
			System.out.println("总分："+totalPoint+"； 得分："+defen+"； 扣分："+koufen);
			fkVo.zongfen = totalPoint.toString();
			fkVo.koufen = koufen.toString();
			fkVo.defen = defen.toString();
			fkVo.xiaoyi = xiaoyi.toString();
			fkVo.standard_result = koufenSB.toString();
			fkVo.reward_result = rewardSB.toString();
			fkVo.deny_result = denySB.toString();
			
			freeConnection(conn);
			return fkVo;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return null;
	}
	public FanKuiOrganizationVo reloadKeZhuRenFankui(FanKuiOrganizationVo fkVo) {

		try
		{
			Connection conn = getConnect();
			
			BigDecimal totalPoint = new BigDecimal(0);//总分
			BigDecimal defen = new BigDecimal(0);//得分
			BigDecimal koufen = new BigDecimal(0);//扣分
//			BigDecimal xiaoyi = new BigDecimal(0);//效益浮动
			BigDecimal jintie = new BigDecimal(0);//主任津贴
			BigDecimal yuan = new BigDecimal(0);//yuan
			BigDecimal yuanmeiren = new BigDecimal(0);//yuan/ren
			StringBuffer koufenSB = new StringBuffer();//扣分说明
			StringBuffer rewardSB = new StringBuffer();//奖惩说明
			StringBuffer denySB = new StringBuffer();//一票否决说明
			
			
			//扣分原因
			String sql = "select distinct a.display_name,a.point,frequence," +
				"	case 	" +
				"		when update_score is not null then update_score" +
				"		when frequence = '0001' and month != 12 then point" +
				" 		when frequence = '0002' and month not in(6,12) then point" +
				"		when frequence = '0003' and month not in(3,6,9,12) then point" +
				"		when score is null then 0" +
				"		else score end as update_score," +
	//			"	case	" +
	//			"		when frequence='0001' then '年' " +
	//			"		when frequence='0002' then '半年'" +
	//			"		when frequence='0003' then '季度'" +
	//			"		when frequence='0004' then '月'" +
	//			"	else '' end as frequence_name," +
				"	case " +
				"		when update_score is not null then update_score" +
				"		when score is null then 0" +
				"		else score end as score," +
				"	b.kpi_id,b.kaohe_id," +
				"	a.description,b.value,a.detail_type" +
				"	from KPI_VS_ORGANIZATION as a" +
				"	left join" +
				"	KPI_Value as b" +
				"	on a.kpi_id = b.kpi_id" +
				"	and a.kaohe_id = b.kaohe_id	" +
				"	and year = " +(fkVo.stat_time_str.split("-")[0])+
				"	and month =" +(fkVo.stat_time_str.split("-")[1])+
				"	where" +
				"	a.kaohe_id = " +fkVo.organization_id+
				"	and standard_type = 0" +
				"	order by frequence desc,display_name";
//			String sql = "select vs.kaohe_id,vs.kpi_id," +
//				" d.id,d.item_name,d.display_name,d.description,d.standard_type,d.detail_type,d.frequence," +
//				" d.point ,v.value,v.score,v.update_score,v.detail,v.valid_detail,v.valid" +
//				" from(" +
//				" select distinct kaohe_id,kpi_id from KPI_VS_ORGANIZATION" +
//				" where kaohe_id = " +fkVo.organization_id+
//				" ) as vs" +
//				" left join DICT_KPI_ITEM as d" +
//				" on d.id = vs.kpi_id" +
//				" left join KPI_VALUE as v" +
//				" on cast(cast(v.year as char)+'-'+cast(v.month as char)+'-1' as datetime) = '"+fkVo.stat_time_str+"'" +
//				" and v.kpi_id = d.id" +
//				" and v.kaohe_id = vs.kaohe_id" +
//	//			" and v.valid = 1" +
//				" where d.standard_type = 0" ;
			ResultSet rs = queryData(conn, sql);
			int x = 0;
			while(rs.next()){
				BigDecimal p_point = rs.getBigDecimal("point");
				BigDecimal p_score = rs.getBigDecimal("score");
				BigDecimal p_update_score = rs.getBigDecimal("update_score");
				BigDecimal p_value = rs.getBigDecimal("value");
				String p_description = rs.getString("description");
				String p_display_name = rs.getString("display_name");
				String frequence = rs.getString("frequence");
				int detail_type = rs.getInt("detail_type");
				int kaohe_id = rs.getInt("kaohe_id");
				int kpi_id = rs.getInt("kpi_id");
				
				
				if(isFullPoint(fkVo.stat_time, frequence))//非本季度，给满分
				{
					totalPoint = totalPoint.add(p_point).setScale(2,BigDecimal.ROUND_HALF_UP);					
				}
				else
				{
					if(p_point != null)
					{
						totalPoint = totalPoint.add(p_point).setScale(2,BigDecimal.ROUND_HALF_UP);
					}
					
					if(p_update_score != null)
					{
						defen = defen.add(p_update_score).setScale(2,BigDecimal.ROUND_HALF_UP);
						koufen = koufen.add(p_point.subtract(p_update_score).setScale(2,BigDecimal.ROUND_HALF_UP)).setScale(2,BigDecimal.ROUND_HALF_UP);
					}
					else if(p_score != null)
					{
						defen = defen.add(p_score).setScale(2,BigDecimal.ROUND_HALF_UP);
						koufen = koufen.add(p_point.subtract(p_score).setScale(2,BigDecimal.ROUND_HALF_UP)).setScale(2,BigDecimal.ROUND_HALF_UP);
					}
					else
					{
						koufen = koufen.add(p_point).setScale(2,BigDecimal.ROUND_HALF_UP);
					}
					
					
					
					if(p_point != null && p_score != null)
					{
						BigDecimal b = new BigDecimal(0);
						if(p_update_score != null)
						{
							b = p_update_score;
						}
						else
						{
							b = p_score;
						}
						if(p_point.compareTo(b)!=0)
						{
							x++;
							koufenSB.append(x+". "+p_display_name+" 扣"+(p_point.subtract(b).setScale(2,BigDecimal.ROUND_HALF_UP))+"分。 ["+p_description+"]\r");
							if(detail_type == 2)//明细
							{
								String sql2 = "select vs.kaohe_id," +
									"	(select KPI_DETAIL_VS_ORGANIZATION.parent_id from KPI_DETAIL_VS_ORGANIZATION where kpi_detail_id = vs.kpi_detail_id and kaohe_id = vs.kaohe_id) as kpi_id,vs.kpi_detail_id," +
									"	(select display_name from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as display_name,  " +
									"	(select target_value from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as target_value," +
									"	(select danwei from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as danwei," +
									"	vs.value,vs.score     from KPI_DETAIL_VALUE as vs" +
									"	where cast(cast(vs.year as char)+'-'+cast(vs.month as char)+'-1' as datetime) = '"+fkVo.stat_time_str+"'" +
									"  	and score < 0" +
									"	and kpi_detail_id in" +
									"	(select kpi_detail_id from KPI_DETAIL_VS_ORGANIZATION	where parent_id = "+kpi_id+" and kaohe_id = "+kaohe_id+")	 ";
								ResultSet rs2 = queryData(conn,sql2);
//								ResultSet rs2 = queryData(conn, "select vs.kaohe_id,vs.kpi_id,vs.kpi_detail_id," +
//										"	(select display_name from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as display_name," +
//										"	(select target_value from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as target_value," +
//										"	(select danwei from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as danwei," +
//										"	value,score" +
//										"	from KPI_VS_ORGANIZATION  as vs" +
//										"	left join KPI_DETAIL_VALUE as v" +
//										"	on vs.kpi_detail_id = v.kpi_detail_id" +
//										"	and cast(cast(v.year as char)+'-'+cast(v.month as char)+'-1' as datetime) = '"+fkVo.stat_time_str+"'" +
//										"	where score <= 0");
								while(rs2.next())
								{
									String c_display_name = rs2.getString("display_name");
									BigDecimal target_value = rs2.getBigDecimal("target_value");
									BigDecimal c_value = rs2.getBigDecimal("value");
									BigDecimal c_score = rs2.getBigDecimal("score");
									koufenSB.append("     "+c_display_name+" 目标值："+target_value.setScale(2,BigDecimal.ROUND_HALF_UP)+"，实际值："+c_value+"，扣除"+c_score+"分。\r");
								}
							}
						}		
					}
				}
			}
			
			//单项奖惩 得分不等于0就有要统计
			sql = "select distinct a.display_name,a.point,frequence," +
				"	case 	" +
				"		when update_score is not null then update_score" +
				"		when frequence = '0001' and month != 12 then point" +
				" 		when frequence = '0002' and month not in(6,12) then point" +
				"		when frequence = '0003' and month not in(3,6,9,12) then point" +
				"		when score is null then 0" +
				"		else score end as update_score," +
	//			"	case	" +
	//			"		when frequence='0001' then '年' " +
	//			"		when frequence='0002' then '半年'" +
	//			"		when frequence='0003' then '季度'" +
	//			"		when frequence='0004' then '月'" +
	//			"	else '' end as frequence_name," +
				"	case " +
				"		when update_score is not null then update_score" +
				"		when score is null then 0" +
				"		else score end as score," +
				"	b.kpi_id,b.kaohe_id," +
				"	a.description,b.value,a.detail_type,a.section_detail,a.detail_type," +
				"	(select danwei from DICT_KPI_ITEM where id = a.kpi_id) as danwei" +
				"	from KPI_VS_ORGANIZATION as a" +
				"	left join" +
				"	KPI_Value as b" +
				"	on a.kpi_id = b.kpi_id" +
				"	and a.kaohe_id = b.kaohe_id	" +
				"	and year = " +(fkVo.stat_time_str.split("-")[0])+
				"	and month =" +(fkVo.stat_time_str.split("-")[1])+
				"	where" +
				"	a.kaohe_id = " +fkVo.organization_id+
				"	and standard_type = 1" +
				"	order by frequence desc,display_name";
//			rs = queryData(conn,"select vs.kaohe_id,vs.kpi_id," +
//					" d.id,d.item_name,d.display_name,d.description,d.standard_type,d.detail_type,d.frequence,d.section_detail,d.danwei," +
//					" d.point ,v.value,v.score,v.update_score,v.detail,v.valid_detail,v.valid" +
//					" from(" +
//					" select distinct kaohe_id,kpi_id from KPI_VS_ORGANIZATION" +
//					" where kaohe_id = " +fkVo.organization_id+
//					" ) as vs" +
//					" left join DICT_KPI_ITEM as d" +
//					" on d.id = vs.kpi_id" +
//					" left join KPI_VALUE as v" +
//					" on cast(cast(v.year as char)+'-'+cast(v.month as char)+'-1' as datetime) = '"+fkVo.stat_time_str+"'" +
//					" and v.kpi_id = d.id" +
//					" and v.kaohe_id = vs.kaohe_id" +
////					" and v.valid = 1" +
//					" where d.standard_type = 1" );
			
			x = 0;
			while(rs.next()){	
//				0：元     [区间（第5个）]
//				1：元/人
//				2：效益％
				BigDecimal p_score = rs.getBigDecimal("score");
				BigDecimal p_update_score = rs.getBigDecimal("update_score");
				BigDecimal p_value = rs.getBigDecimal("value");
				String section = rs.getString("section_detail");
				String frequence = rs.getString("frequence");
				int danwei = rs.getInt("danwei");
				String danwei_s = "";
				String p_description = rs.getString("description");
				String p_display_name = rs.getString("display_name");
				int detail_type = rs.getInt("detail_type");
				int kpi_id = rs.getInt("kpi_id");
				int kaohe_id = rs.getInt("kaohe_id");
				
				if(isFullPoint(fkVo.stat_time, frequence))
				{
					continue;
				}
				
				if(p_score != null)//得分非空
				{
					BigDecimal tmpScore = new BigDecimal(0);
					if(p_update_score != null && p_update_score.compareTo(tmpScore)!=0)
					{
						tmpScore = p_update_score.setScale(2,BigDecimal.ROUND_HALF_UP);
					}
					else if(p_score.compareTo(tmpScore)!=0)
					{
						tmpScore = p_score.setScale(2,BigDecimal.ROUND_HALF_UP);
					}
					else//score = 0,不用处理
					{
						continue;
					}

					if(detail_type == 0)//标准型
					{
						if(danwei == 0)
						{
							danwei_s = "元";
							yuan = yuan.add(tmpScore).setScale(2,BigDecimal.ROUND_HALF_UP);
						}
						else if(danwei == 1)
						{
							danwei_s = "元/人";
							yuanmeiren = yuanmeiren.add(tmpScore).setScale(2,BigDecimal.ROUND_HALF_UP);
						}
						else if(danwei == 2)
						{
							danwei_s = "效益％";
							jintie = jintie.add(tmpScore).setScale(2,BigDecimal.ROUND_HALF_UP);
						}
						x++;
						rewardSB.append(x+". "+p_display_name+" "+tmpScore+danwei_s+"。 ["+p_description+"]\r");
					}
					else if(detail_type == 1)//区间型
					{
						String dw = section.split(";")[0].split(",")[4];
						if(dw.equals("0"))
						{
							danwei_s = "元";
							yuan = yuan.add(tmpScore).setScale(2,BigDecimal.ROUND_HALF_UP);
						}
						else if(dw.equals("1"))
						{
							danwei_s = "元/人";
							yuanmeiren = yuanmeiren.add(tmpScore).setScale(2,BigDecimal.ROUND_HALF_UP);
						}
						else if(dw.equals("2"))
						{
							danwei_s = "效益％";
							jintie = jintie.add(tmpScore).setScale(2,BigDecimal.ROUND_HALF_UP);
						}
						x++;
						rewardSB.append(x+". "+p_display_name+" "+tmpScore+danwei_s+"。 ["+p_description+"]\r");
						
					}
					else//明细型
					{
						x++;
						rewardSB.append(x+". "+p_display_name+" "+tmpScore+danwei_s+"。 ["+p_description+"]\r");
						String sql2 = "select vs.kaohe_id," +
							"	(select KPI_DETAIL_VS_ORGANIZATION.parent_id from KPI_DETAIL_VS_ORGANIZATION where kpi_detail_id = vs.kpi_detail_id and kaohe_id = vs.kaohe_id) as kpi_id,vs.kpi_detail_id," +
							"	(select display_name from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as display_name,  " +
							"	(select target_value from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as target_value," +
							"	(select danwei from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as danwei," +
							"	vs.value,vs.score,vs.update_score     from KPI_DETAIL_VALUE as vs" +
							"	where cast(cast(vs.year as char)+'-'+cast(vs.month as char)+'-1' as datetime) = '"+fkVo.stat_time_str+"'  and score < 0" +
							"	and kpi_detail_id in" +
							"	(select kpi_detail_id from KPI_DETAIL_VS_ORGANIZATION	where parent_id = "+kpi_id+" and kaohe_id = "+kaohe_id+")	 ";
						ResultSet rs2 = queryData(conn,sql2);
//						ResultSet rs2 = queryData(conn, "select vs.kaohe_id,vs.kpi_id,vs.kpi_detail_id," +
//								"	(select display_name from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as display_name," +
//								"	(select target_value from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as target_value," +
//								"	(select danwei from DICT_KPI_ITEM_DETAIL where id = vs.kpi_detail_id) as danwei," +
//								"	value,score,update_score" +
//								"	from KPI_VS_ORGANIZATION  as vs" +
//								"	left join KPI_DETAIL_VALUE as v" +
//								"	on vs.kpi_detail_id = v.kpi_detail_id" +
//								"	and cast(cast(v.year as char)+'-'+cast(v.month as char)+'-1' as datetime) = '"+fkVo.stat_time_str+"'" +
//								"	where score != 0");
						while(rs2.next())
						{
							String c_display_name = rs2.getString("display_name");
							BigDecimal target_value = rs2.getBigDecimal("target_value");
							BigDecimal c_value = rs2.getBigDecimal("value");
							BigDecimal c_score = rs2.getBigDecimal("score");
							BigDecimal c_update_score = rs2.getBigDecimal("update_score");
							int c_danwei = rs2.getInt("danwei");
							BigDecimal tmp_score = new BigDecimal(0);
							if(c_update_score !=null && c_update_score.compareTo(tmp_score)!=0)
							{
								tmp_score = c_update_score.setScale(2,BigDecimal.ROUND_HALF_UP);
							}
							else if(c_score !=null && c_score.compareTo(tmp_score)!=0)
							{
								tmp_score = c_score.setScale(2,BigDecimal.ROUND_HALF_UP);
							}

							if(c_danwei == 0)
							{
								rewardSB.append("     "+c_display_name+" "+tmp_score+"元。\r");
							}
							else if(c_danwei == 1)
							{
								rewardSB.append("     "+c_display_name+" "+tmp_score+"元/人。\r");
							}
							else
							{
								rewardSB.append("     "+c_display_name+" "+tmp_score+"效益％。\r");
							}
							
						}
					}
				}
			}
			
			//一票否决
			sql = "select distinct a.display_name,a.point,frequence," +
				"	case 	" +
				"		when update_score is not null then update_score" +
				"		when frequence = '0001' and month != 12 then point" +
				" 		when frequence = '0002' and month not in(6,12) then point" +
				"		when frequence = '0003' and month not in(3,6,9,12) then point" +
				"		when score is null then 0" +
				"		else score end as update_score," +
	//			"	case	" +
	//			"		when frequence='0001' then '年' " +
	//			"		when frequence='0002' then '半年'" +
	//			"		when frequence='0003' then '季度'" +
	//			"		when frequence='0004' then '月'" +
	//			"	else '' end as frequence_name," +
				"	case " +
				"		when update_score is not null then update_score" +
				"		when score is null then 0" +
				"		else score end as score," +
				"	b.kpi_id,b.kaohe_id," +
				"	a.description,b.value,a.detail_type,a.detail_type," +
				"	(select danwei from DICT_KPI_ITEM where id = a.kpi_id) as danwei" +
				"	from KPI_VS_ORGANIZATION as a" +
				"	left join" +
				"	KPI_Value as b" +
				"	on a.kpi_id = b.kpi_id" +
				"	and a.kaohe_id = b.kaohe_id	" +
				"	and year = " +(fkVo.stat_time_str.split("-")[0])+
				"	and month =" +(fkVo.stat_time_str.split("-")[1])+
				"	where" +
				"	a.kaohe_id = " +fkVo.organization_id+
				"	and standard_type = 2" +
				"	order by frequence desc,display_name";
//			rs = queryData(conn,"select vs.kaohe_id,vs.kpi_id," +
//					" d.id,d.item_name,d.display_name,d.description,d.standard_type,d.detail_type,d.frequence,d.danwei," +
//					" d.point ,v.value,v.score,v.update_score,v.detail,v.valid_detail,v.valid" +
//					" from(" +
//					" select distinct kaohe_id,kpi_id from KPI_VS_ORGANIZATION" +
//					" where kaohe_id = " +fkVo.organization_id+
//					" ) as vs" +
//					" left join DICT_KPI_ITEM as d" +
//					" on d.id = vs.kpi_id" +
//					" left join KPI_VALUE as v" +
//					" on cast(cast(v.year as char)+'-'+cast(v.month as char)+'-1' as datetime) = '"+fkVo.stat_time_str+"'" +
//					" and v.kpi_id = d.id" +
//					" and v.kaohe_id = vs.kaohe_id" +
////					" and v.valid = 1" +
//					" where d.standard_type = 2" );
			
			x = 0;
			while(rs.next()){
				BigDecimal p_score = rs.getBigDecimal("score");
				BigDecimal p_update_score = rs.getBigDecimal("update_score");
				BigDecimal p_value = rs.getBigDecimal("value");
				String p_description = rs.getString("description");
				String p_display_name = rs.getString("display_name");
				String frequence = rs.getString("frequence");
				int detail_type = rs.getInt("detail_type");
				int danwei = rs.getInt("danwei");
				String danwei_s = "";
				if(danwei == 0)
				{
					danwei_s = "元";
				}
				else if(danwei == 1)
				{
					danwei_s = "元/人";
				}
				else
				{
					danwei_s = "效益％";
				}
				
				if(isFullPoint(fkVo.stat_time, frequence))
				{
					continue;
				}
				else
				{
					BigDecimal tmp_score = new BigDecimal(0);
					if(detail_type == 0)//基本
					{
						if(p_update_score != null && p_update_score.compareTo(tmp_score)!=0)
						{
							tmp_score = p_update_score.setScale(2,BigDecimal.ROUND_HALF_UP);
						}
						else if(p_score != null && p_score.compareTo(tmp_score)!=0)
						{
							tmp_score = p_score.setScale(2,BigDecimal.ROUND_HALF_UP);
						}
						else
						{
							continue;
						}
						x++;
						denySB.append(x+". "+p_display_name+"   提交"+p_value+"，  "+tmp_score+danwei_s+"。 ["+p_description+"]\r");
					}
					else if(p_value != null && p_value.compareTo(tmp_score)!=0)//处理意见
					{
						x++;
						denySB.append(x+". "+p_display_name+"   发生"+p_value+"起。["+p_description+"]\r");
					}
				}
				
			}
			
			
			
			System.out.println("总分："+totalPoint+"； 得分："+defen+"； 扣分："+koufen);
			fkVo.zongfen = totalPoint.toString();
			fkVo.koufen = koufen.toString();
			fkVo.defen = defen.toString();
			fkVo.jintie = jintie.toString();
			fkVo.standard_result = koufenSB.toString();
			fkVo.reward_result = rewardSB.toString();
			fkVo.deny_result = denySB.toString();
			
			freeConnection(conn);
			return fkVo;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return null;
	
	}

	private boolean isFullPoint(Date stat_time,String frequence)
	{
		if(frequence.equals("0002"))
		{
			if(stat_time.getMonth() == 2 || 
				stat_time.getMonth() == 5||
				stat_time.getMonth() == 8||
				stat_time.getMonth() == 11)	
			{
				return true;
			}
		}
		else if(frequence.equals("0003"))
		{
			if(stat_time.getMonth() == 5||
				stat_time.getMonth() == 11)	
			{
				return true;
			}
		}
		else if(frequence.equals("0003") && stat_time.getMonth() == 11)
		{
			return true;
		}
		return false;
	}
	public void saveKeShiFankui(FanKuiOrganizationVo fkVo) {
		try
		{
			Connection conn = getConnect();
			int id = 0;
			if(fkVo.id != 0)// 存在，删除
			{
				id = fkVo.id;
				updateData(conn, "delete FANKUI_ORGANIZATION where id = "+fkVo.id);
			}
			else
			{
				id = getNewMaxId(conn, "FANKUI_ORGANIZATION");
			}
			//插入
			
			
			updateData(conn, "insert into FANKUI_ORGANIZATION " +
					"	(id,organization_id,stat_time,type," +
					"	zongfen,koufen,defen,xiaoyi,jintie," +
					"	standard_result,reward_result,deny_result,other_result,opinion) " +
					"	values" +
					"	("+id+","+fkVo.organization_id+",'"+fkVo.stat_time_str+"',1,"+
					"	"+DataFormater.sqlBigDecimal(fkVo.zongfen)+"," +
					"	"+DataFormater.sqlBigDecimal(fkVo.koufen)+"," +
					"	"+DataFormater.sqlBigDecimal(fkVo.defen)+"," +
					"	"+DataFormater.sqlBigDecimal(fkVo.xiaoyi)+"," +
					"	"+DataFormater.sqlBigDecimal(fkVo.jintie)+"," +
					"	"+DataFormater.sqlString(fkVo.standard_result)+"," +
					"	"+DataFormater.sqlString(fkVo.reward_result)+"," +
					"	"+DataFormater.sqlString(fkVo.deny_result)+"," +
					"	"+DataFormater.sqlString(fkVo.other_result)+"," +
					"	"+DataFormater.sqlString(fkVo.opinion)+")" );
			freeConnection(conn);
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
	public void saveKeZhuRenFankui(FanKuiOrganizationVo fkVo) {
		try
		{
			Connection conn = getConnect();
			int id = 0;
			if(fkVo.id != 0)// 存在，删除
			{
				id = fkVo.id;
				updateData(conn, "delete FANKUI_ORGANIZATION where id = "+fkVo.id);
			}
			else
			{
				id = getNewMaxId(conn, "FANKUI_ORGANIZATION");
			}
			//插入
			
			String sql = "insert into FANKUI_ORGANIZATION " +
				"	(id,organization_id,stat_time,type," +
				"	koufen,defen,xiaoyi,jintie," +
				"	standard_result,reward_result,deny_result,other_result,opinion) " +
				"	values(" +
				"	"+id+","+fkVo.organization_id+",'"+fkVo.stat_time_str+"',2,"+
				"	"+DataFormater.sqlBigDecimal(fkVo.koufen)+"," +
				"	"+DataFormater.sqlBigDecimal(fkVo.defen)+"," +
				"	"+DataFormater.sqlBigDecimal(fkVo.xiaoyi)+"," +
				"	"+DataFormater.sqlBigDecimal(fkVo.jintie)+"," +
				"	"+DataFormater.sqlString(fkVo.standard_result)+"," +
				"	"+DataFormater.sqlString(fkVo.reward_result)+"," +
				"	"+DataFormater.sqlString(fkVo.deny_result)+"," +
				"	"+DataFormater.sqlString(fkVo.other_result)+"," +
				"	"+DataFormater.sqlString(fkVo.opinion)+")";
			updateData(conn,  sql);
			freeConnection(conn);
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
	
	public FanKuiOrganizationVo reloadFankui(FanKuiOrganizationVo fkVo,Connection conn){
		try{
			
			int y = Integer.valueOf(fkVo.stat_time_str.split("-")[0]);
			int m = Integer.valueOf(fkVo.stat_time_str.split("-")[1]);
			int kaohe_id = fkVo.organization_id;
			
			BigDecimal totalPoint = new BigDecimal(0);//总分
			BigDecimal defen = new BigDecimal(0);//得分
			BigDecimal koufen = new BigDecimal(0);//扣分
			BigDecimal xiaoyi = new BigDecimal(0);//效益浮动 == 主任津贴
			BigDecimal jintie = new BigDecimal(0);//主任津贴
			BigDecimal yuan = new BigDecimal(0);//yuan
			BigDecimal yuanmeiren = new BigDecimal(0);//yuan/ren
			StringBuffer koufenSB = new StringBuffer();//扣分说明
			StringBuffer rewardSB = new StringBuffer();//奖惩说明
			StringBuffer denySB = new StringBuffer();//一票否决说明
			
			String sql = "";
			ResultSet rs;
			if(conn == null || conn.isClosed()){
				conn = getConnect();
			}
			
			//计算满分
			sql = "select sum(point) from KPI_VS_ORGANIZATION where standard_type = 0 and kaohe_id = "+kaohe_id;
			rs = queryData(conn, sql);
			if(rs.next()){
				totalPoint = new BigDecimal(rs.getInt(1));
			}
			
			
			//计算每条数据
						
			sql = "select "+y+" as year,"+m+" as month," +
			//"	ko.kaohe_id,duty1.name+'-'+p_kh.name as kaohe_name," +
			//"	case when ko.guikou_id != -1 then ko.guikou_id " +
			//"		else kdo.guikou_id end as guikou_id," +
			//" 	case when ko.guikou_id != -1 then duty2.name+'-'+p_gk.name" +
			//"		else duty4.name+'-'+c_gk.name end as guikou_name," +
			//"	kdo.kaohe_id as c_kaohe_id,duty3.name+'-'+c_kh.name as c_kaohe_name," +
			//"	kdo.guikou_id as c_guikou_id,duty4.name+'-'+c_gk.name as c_guikou_name," +
			"	(select sum(p.value) from kpi_detail_value as p  where p.year = "+y+" and p.month = " +m+
			"	and p.kaohe_id = kdo.kaohe_id and p.parent_id = kdo.parent_id) as c_sum_value,"+
			"	(select sum(p.score) from kpi_detail_value as p  where p.year = "+y+" and p.month = " +m+
			"	and p.kaohe_id = kdo.kaohe_id and p.parent_id = kdo.parent_id) as c_sum_score," +
			//"	case " +
			//"	when ko.detail_type = 2 and kdo.target_type = 1 and kdv.value < kdo.target_value then 2" +
			//"	when ko.detail_type = 2 and kdo.target_type = 2 and kdv.value > kdo.target_value then 2" +
			//"	when ko.detail_type = 2 and kdo.target_type = 0 and kdv.value is not null then 2" +
			//"	when ko.detail_type = 3 and kdv.value is not null then 2" +
			//"	when ko.detail_type != 2 and ko.target_type = 1 and kv.value < ko.target_value then 2" +
			//"	when ko.detail_type != 2 and ko.target_type = 2 and kv.value > ko.target_value then 2" +
			//"	when ko.detail_type != 2 and ko.target_type = 0 and kv.value is not null then 2" +
			//"	else 1 end as dabiao,"+
			
			"	ko.kpi_id as kpi_id,ko.item_name as item_name,ko.display_name as display_name," +
			"	ko.description as description,ko.point as point,ko.target_value as target_value," +
			"	ko.frequence as frequence,ko.target_type as target_type,ko.standard_type as standard_type," +
			"	ko.detail_type as detail_type,ko.different_scale as different_scale,ko.add_type as add_type," +
			"	ko.scale_point as scale_point,ko.formula as formula,ko.default_value as default_value," +
			"	ko.section_detail as section_detail,ko.danwei as danwei," +
			
			"	kdo.kpi_detail_id as c_id,kdo.item_name as c_item_name,kdo.display_name as c_display_name," +
			"	kdo.target_value as c_target_value,kdo.default_value as c_default_value," +
			"	kdo.target_type as c_target_type,kdo.different_scale as c_different_scale,kdo.add_type as c_add_type," +
			"	kdo.scale_point as c_scale_point,kdo.formula as c_formula,kdo.no_double as c_no_double," +
			"	kdo.danwei as c_danwei," +
			
			"	kv.id as v_id,kv.value as value,kv.create_user as create_user,kv.create_time as create_time," +
			"	kv.score as score,kv.update_score as update_score,kv.update_user as update_user," +
			"	kv.update_time as update_time,kv.update_description as update_description," +
			"	kv.valid as valid,kv.valid_user as valid_user,kv.valid_time as valid_time," +
			"	kv.detail as detail,kv.valid_detail as valid_detail," +

			"	kdv.id as vc_id,kdv.value as c_value," +
			"	kdv.score as c_score,kdv.create_user as c_create_user,kdv.create_time as c_create_time," +
			"	kdv.update_score as c_update_score,kdv.update_user as c_update_user,kdv.update_time as c_update_time," +
			"	kdv.valid as c_valid,kdv.valid_user as c_valid_user,kdv.valid_time as c_valid_time," +
			"	kdv.update_description as c_update_description," +
			"	kdv.detail as c_detail,kdv.valid_detail as c_valid_detail" +
			
			"	from KPI_VS_ORGANIZATION as ko" +
			"	left join KPI_DETAIL_VS_ORGANIZATION as kdo" +
			"	on  ko.kpi_id = kdo.parent_id" +
			"	and ko.kaohe_id = kdo.kaohe_id" +
			"	left join KPI_VALUE as kv" +
			"	on kv.year = "+y+" and kv.month = " +m+
			"	and kv.kpi_id = ko.kpi_id and kv.kaohe_id = ko.kaohe_id and kv.guikou_id = ko.guikou_id" +
			"	left join KPI_DETAIL_VALUE as kdv" +
			"	on kdv.year = "+y+" and kdv.month = " +m+
			"	and kdv.kaohe_id = kdo.kaohe_id and kdv.guikou_id = kdo.guikou_id and kdv.kpi_detail_id = kdo.kpi_detail_id" +
			//"	left join t_sys_stat_department as p_kh" +
			//"	on ko.kaohe_id = p_kh.id" +
			//" 	left join dict_duty_type as duty1" +
			//"	on duty1.id = p_kh.duty_id" +
			//"	left join t_sys_stat_department as p_gk" +
			//"	on ko.guikou_id = p_gk.id" +
			//"	left join dict_duty_type as duty2" +
			//"	on duty2.id = p_gk.duty_id" +
			//"	left join t_sys_stat_department as c_kh" +
			//"	on kdo.kaohe_id = c_kh.id" +
			//" 	left join dict_duty_type as duty3" +
			//"	on duty3.id = c_kh.duty_id" +
			//"	left join t_sys_stat_department as c_gk" +
			//"	on kdo.guikou_id = c_gk.id" +
			//"	left join dict_duty_type as duty4" +
			//"	on duty4.id = c_gk.duty_id" +
			"	where  ko.kaohe_id = "+kaohe_id+
			"	order by kpi_id,c_id";
			
			rs = queryData(conn, sql);
			int standard_type;
			int detail_type;
			int standard_id = 0;
			int danxiang_id = 0;
			int yipiao_id = 0;
			while(rs.next()){
				standard_type = rs.getInt("standard_type"); 
				detail_type = rs.getInt("detail_type");
				if(standard_type == 0){//基本  不提交就不扣分,算满分
					standard_id ++;
					if(detail_type == 2){
						if(rs.getBigDecimal("c_score") == null){
							koufenSB.append(standard_id+"."+rs.getString("display_name")+"["+rs.getString("c_display_name")+"]"+
									"	标准值:"+rs.getBigDecimal("c_target_value")+",实际:未提交;满分:"+rs.getBigDecimal("point")+"分." +
									"\r	[评价标准"+rs.getString("description")+"]\r\n");	
						}else if(rs.getBigDecimal("c_score").compareTo(new BigDecimal(0)) != 0 ){
							koufenSB.append(standard_id+"."+rs.getString("display_name")+"["+rs.getString("c_display_name")+"]"+
									"	标准值:"+rs.getBigDecimal("c_target_value")+",实际:+"+rs.getBigDecimal("c_value")+";满分:"+rs.getBigDecimal("point")+"分,扣"+rs.getBigDecimal("c_score")+"分." +
									"\r	[评价标准"+rs.getString("description")+"]\r\n");	
							koufen.add(rs.getBigDecimal("c_score"));
						}
					}else{
						if(rs.getBigDecimal("score") == null){
							koufenSB.append(standard_id+"."+rs.getString("display_name")+
									"	标准值:"+rs.getString("target_value")+",实际:未提交;满分:"+rs.getString("point")+"分." +
									"\r	[评价标准:"+rs.getString("description")+"]\r\n");
						}else if(rs.getBigDecimal("score").compareTo( rs.getBigDecimal("point")) != 0 ){
							koufenSB.append(standard_id+"."+rs.getString("display_name")+
									"	标准值:"+rs.getString("target_value")+",实际:"+rs.getString("value")+";满分:"+rs.getString("point")+"分,扣"+rs.getBigDecimal("point").multiply(rs.getBigDecimal("score"))+"分." +
									"\r	[评价标准:"+rs.getString("description")+"]\r\n");
							koufen.add(rs.getBigDecimal("point").multiply(rs.getBigDecimal("score")));
						}
					}
				}else if(standard_type == 1){//单项奖惩
//					0：元     [区间（第5个）]
//					1：元/人
//					2：效益％
					danxiang_id ++;
					String danwei_s = "";
					int danwei;
					if(detail_type == 2){
						danwei = rs.getInt("c_danwei");
						if(rs.getBigDecimal("c_score") == null){							
							rewardSB.append(danxiang_id+". "+rs.getString("display_name")+" ["+rs.getString("c_display_name")+"] 未提交\r\n");
						}
						else if(rs.getBigDecimal("c_score") != null && rs.getBigDecimal("c_score").compareTo(new BigDecimal(0)) != 0 ){
							if(danwei == 0)
							{
								danwei_s = "元";
								yuan = yuan.add(rs.getBigDecimal("c_score")).setScale(2,BigDecimal.ROUND_HALF_UP);
							}
							else if(danwei == 1)
							{
								danwei_s = "元/人";
								yuanmeiren = yuanmeiren.add(rs.getBigDecimal("c_score")).setScale(2,BigDecimal.ROUND_HALF_UP);
							}
							else if(danwei == 2)
							{
								danwei_s = "效益％";
								xiaoyi = xiaoyi.add(rs.getBigDecimal("c_score")).setScale(2,BigDecimal.ROUND_HALF_UP);
							}
							rewardSB.append(danxiang_id+". "+rs.getString("display_name")+"["+rs.getString("c_display_name")+"]"+"   "+rs.getBigDecimal("c_score")+danwei_s+"。 ["+rs.getString("description")+"]\r\n");
						}
					}else{
						danwei = rs.getInt("danwei");
						if(rs.getBigDecimal("score") == null){
							rewardSB.append(danxiang_id+". "+rs.getString("display_name")+" 未提交\r\n");
						}
						else if(rs.getBigDecimal("score") != null && rs.getBigDecimal("score").compareTo(new BigDecimal(0)) != 0 ){
							if(danwei == 0)
							{
								danwei_s = "元";
								yuan = yuan.add(rs.getBigDecimal("score")).setScale(2,BigDecimal.ROUND_HALF_UP);
							}
							else if(danwei == 1)
							{
								danwei_s = "元/人";
								yuanmeiren = yuanmeiren.add(rs.getBigDecimal("score")).setScale(2,BigDecimal.ROUND_HALF_UP);
							}
							else if(danwei == 2)
							{
								danwei_s = "效益％";
								xiaoyi = xiaoyi.add(rs.getBigDecimal("score")).setScale(2,BigDecimal.ROUND_HALF_UP);
							}
							rewardSB.append(danxiang_id+". "+rs.getString("display_name")+" "+rs.getBigDecimal("score")+danwei_s+"。 ["+rs.getString("description")+"]\r\n");
							
						}
					}
				}else if(standard_type == 2){//一票否决 没有明细
					yipiao_id++;
					int danwei = rs.getInt("danwei");
					if(rs.getBigDecimal("score") == null){
						denySB.append(yipiao_id+". "+rs.getString("display_name")+" 未提交.\r\n");
					}
					else if(rs.getBigDecimal("score") != null && rs.getBigDecimal("score").compareTo(new BigDecimal(0)) != 0 ){
						String danwei_s = "";
						if(danwei == 0)
						{
							danwei_s = "元";
						}
						else if(danwei == 1)
						{
							danwei_s = "元/人";
						}
						else
						{
							danwei_s = "效益％";
						}
						
						if(detail_type == 0){//基本
							denySB.append(yipiao_id+". "+rs.getString("display_name")+"   提交"+rs.getBigDecimal("value")+"，  "+rs.getBigDecimal("score")+danwei_s+"。 ["+rs.getString("description")+"]\r\n");
						}
						else{//处理意见
							denySB.append(yipiao_id+". "+rs.getString("display_name")+"   发生"+rs.getBigDecimal("value")+"起。   ["+rs.getString("description")+"]\r\n");
						}
					}
				}
			}
			fkVo.zongfen = totalPoint.toString();
			fkVo.koufen = koufen.toString();
			fkVo.standard_result = koufenSB.toString();
			fkVo.reward_result = rewardSB.toString();
			fkVo.deny_result = denySB.toString();
			fkVo.defen = totalPoint.multiply(koufen).toString();
			fkVo.jintie = xiaoyi.toString();
			fkVo.xiaoyi = xiaoyi.toString();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return fkVo;
	}
	
}
